const express = require("express");
const router = express.Router();
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const moment = require("moment-timezone");
const axios = require("axios");
const fs = require("fs");

//获取包名列表
router.post("/getFeedbackPkList", async (req, res) => {
    let user = await utils.getUserRole(req, res);
    let roleList = utils.filterFeedbackListByRoleKey(user.roleKey);
    if (user.roleAdmin || user.hasManage) {
        //是管理 无条件  查询所有
        roleList = [];
    }
    let obj = req.body;
    let { page, size } = utils.pageSize(obj.page, obj.size);

    let sqlParams = [];

    let sql = `SELECT package_name FROM app_brand_collection`;

    if (roleList.length > 0) {
        // 添加WHERE子句以仅返回roleList中的包名
        let pkPlaceholders = roleList.map(() => "?").join(", ");
        sql += ` WHERE package_name IN (${pkPlaceholders})`;
        sqlParams.push(...roleList);
    }

    sql += ` GROUP BY package_name LIMIT ?, ?`;
    sqlParams.push(page, size); // 注意，LIMIT子句中的参数调整了位置

    await pool.query(sql, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        const filteredResult = result.filter((item) => item.pk);
        filteredResult.forEach((item) => {
            item.package_name = item.pk;
        });
        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(filteredResult),
            })
        );
    });
});

//查询反馈列表
router.post("/getFeedbackList", async (req, res) => {
    let user = await utils.getUserRole(req, res);
    let roleList = utils.filterFeedbackListByRoleKey(user.roleKey);
    if (user.roleAdmin || user.hasManage) {
        //是管理 无条件  查询所有
        roleList = [];
    }

    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { package_name, state, dateRange } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (package_name) {
        sqlConditions.push("package_name = ?");
        sqlParams.push(package_name);
    }
    if (state) {
        sqlConditions.push("state = ?");
        sqlParams.push(state);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        sqlConditions.push("first_install_time BETWEEN ? AND ?");
        sqlParams.push(dateRange.start, dateRange.end);
    }
    // roleList 条件处理
    if (roleList.length > 0) {
        // 转换roleList为一个适用于SQL IN 子句的字符串
        let roleListStr = roleList.map((item) => "?").join(", ");
        sqlConditions.push(`package_name IN (${roleListStr})`);
        sqlParams.push(...roleList); // 使用扩展运算符将roleList的元素添加为参数
    }
    let sqlBase = `SELECT * FROM app_brand_collection`;
    let totalBase = "FROM app_brand_collection";
    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }
    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            element.package_name = element.package_name;
            element.create_date = moment(element.create_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.first_install_time = moment(element.first_install_time)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(result),
                total: total,
            })
        );
    });
});

//更改反馈状态
router.post("/editFeedbackState", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { id, state } = req.body;
    // 更新
    let sql = `UPDATE js_app_feedback 
             SET state = ?, update_date = ?, update_by = ? 
             WHERE id = ?`;
    let values = [state, utils.formatDate(new Date()), name, id];
    await pool.query(sql, values, (updateErr, updateResult) => {
        if (updateErr)
            return res.send(utils.returnData({ code: -1, updateErr, req }));
        res.send({ msg: "success" });
    });
});

//反馈接口
router.post("/save", async (req, res) => {
    const {
        package_name,
        brand,
        model,
        country,
        isp,
        lang,
        network_type,
        operator,
        sdk,
        uuid,
        ver,
        ip,
        first_install_time
    } = req.body;
    let feedback = {};
    //新增
    feedback.id = utils.createId();
    feedback.package_name = package_name;
    feedback.brand = brand;
    feedback.model = model;
    feedback.lang = lang;
    feedback.ver = ver;
    feedback.uuid = uuid;
    feedback.country = country;
    feedback.sdk = sdk;
    feedback.isp = isp;
    feedback.first_install_time = first_install_time;
    feedback.operator = operator;
    feedback.network_type = network_type;
    feedback.ip = ip;
    feedback.create_date = utils.formatDate(new Date());
    feedback.update_date = utils.formatDate(new Date());
    const { text, values } = utils.createInsertSql("app_brand_collection", feedback);
    let result = await utils.query(text, values);
    if (result.affectedRows == 1) {
        res.send({
            code: 0,
            msg: "操作成功",
        });
    } else {
        res.send(utils.returnData({ code: -1, err, req }));
    }
});
//不分页的评论列表
router.post("/findBrandListAll", async (req, res) => {
    try {
        const { dateRange } = req.body;
        let sql = `FROM app_brand_collection WHERE 1=1`;
        let params = [];
        if (dateRange && dateRange.start && dateRange.end) {
            sql += ` AND update_date BETWEEN ? AND ?`;
            params.push(dateRange.start, dateRange.end);
        }

        // 查询数据的SQL
        let sqlSelect = `SELECT * ${sql} ORDER BY update_date DESC`;

        await pool.query(sqlSelect, params, async (err, result) => {
            if (err) return res.send(utils.returnData({ code: -1, err, req }));

            // 创建一个包含所有异步操作的 Promise 数组
            let promises = result.map(async (element) => {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");

                return element; // 返回处理后的 element
            });

            // 等待所有异步操作完成，并获得结果数组
            let updatedResult = await Promise.all(promises);

            res.send(
                utils.returnData({
                    data: updatedResult, // 返回更新后的结果
                })
            );
        });
    } catch (err) {
        console.log(err)
        res.send(utils.returnData({ code: -1, err, req }));
    }
});
router.post('/statistics', async (req, res) => {
    let sql = `SELECT 
    brand, 
    COUNT(*) as occurrences,
    COUNT(*) / (SELECT COUNT(*) FROM app_brand_collection) * 100 as percentage
FROM app_brand_collection
GROUP BY brand
ORDER BY occurrences DESC
LIMIT 20;`
    await pool.query(sql, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        res.send(
            utils.returnData({
                data: result
            })
        );
    });
})
router.post('/down', async (req, res) => {
    // CSS 内容
 

//     // 使用正则表达式提取所有的 URL
//     const urlRegex = /url\((https:\/\/fonts\.gstatic\.com\/s\/roboto\/v32\/[\w-]+\.woff2)\)/g;
//     let match;
//     const urls = [];

//     // 执行匹配
//     while ((match = urlRegex.exec(cssContent)) !== null) {
//         urls.push(match[1]);
//     }

//     // 逐个下载每个文件
//     urls.forEach((url, index) => {
//         console.log(`Starting download: ${url}`);

//         axios({
//             method: 'get',
//             url: url,
//             responseType: 'stream'
//         })
//             .then(function (response) {
//                 const filePath = `font${index + 1}.woff2`;
//                 const writer = fs.createWriteStream(filePath);

//                 response.data.pipe(writer);

//                 writer.on('finish', () => {
//                     console.log(`Downloaded: ${filePath}`);
//                 });

//                 writer.on('error', (error) => {
//                     console.error('Error writing file:', error);
//                 });
//             })
//             .catch(function (error) {
//                 console.error('Error downloading file:', error.message);
//             });
//     });
})
module.exports = router;
